from connection import POOL
import os


def addbook(name):
    book = []
    path = os.path.relpath(os.path.dirname(__file__))
    # print(path)
    # 读取文件
    with open(path + "\\books\\%s.txt" % name, 'r', encoding='gb18030') as f:
        for line in f.readlines():
            result = list()
            if line.isspace():
                continue
            linelist = line.split()
            result.append(linelist[0])
            translation = ' '.join(linelist[1:])

            result.append(translation)
            book.append(result)

    conn = POOL.connection()
    cur = conn.cursor()

    # 插入管理信息
    name = repr(name)
    sql = '''INSERT INTO booklist (bookname, creator)
            VALUES (%s,%s)''' % (name, 10000)
    cur.execute(sql)
    bid = int(cur.lastrowid)
    conn.commit()

    # 建表
    sql = '''CREATE TABLE IF NOT EXISTS `zhifou_word`.`book_%d`  (
            `word` varchar(255) NOT NULL,
            `translation` varchar(255) NULL,
            `sentence` longtext NULL,
            PRIMARY KEY (`word`)
            )''' % (bid)

    cur.execute(sql)
    conn.commit()

    # 批量插入数据
    sql = '''INSERT ignore INTO book_{} (word, translation)
            VALUES (%s,%s)'''.format(bid)
    # print(sql)
    cur.executemany(sql, book)
    conn.commit()

    # 计算条数
    sql = '''UPDATE booklist SET total_num =(
        SELECT COUNT(*) FROM book_{})
        WHERE bid = {}'''.format(bid, bid)
    cur.execute(sql)
    conn.commit()

    print("添加例句")
    # 添加例句
    sql = '''UPDATE book_{} SET book_{}.sentence =
            (SELECT words.lx FROM words
            WHERE words.Word = book_{}.word)'''.format(bid, bid, bid)
    cur.execute(sql)
    conn.commit()


if __name__ == "__main__":
    addbook("CET6")
    addbook("GRE")
    addbook("SAT")
    addbook("TOEFL")
